CREATE OR REPLACE package oratsg_apidoc as

TYPE T_CLOB_ARRAY IS TABLE OF CLOB;

/*
<main_description>

  creator: Andrey Tsyganov
     date: March, 2012
  contact: atsygan@gmail.com, A.Tsyganov@rug.nl
  (c) 2011, all rights recerved

  Developer of this package is NOT responsible in case of any change of this code by its users
  and also NOT responsible for any bugs in the functionality of the package, however support can be provided

  This package is a basic Web Interface for Version Controll API documentation display.
  Also it can be used for any ither Oracle source object commented with in the way
  that ORATSG_DOC can parse.

  Format of the comments based on tags which structure is:
  <comments>
    <function> (<procedure>)
      name of the procedure/function
    </function> (</procedure)

    -- in case of function
    <return>
      type that function returnes
    </return>

    <description>
      description of the procedure/function
    </description>

    <parameters>
      <IN>
        list of IN parameters in format
        [parameter name] [parameter description] [parameter type] [parameter default value]
      </IN>
      <OUT>
        list of OUT parameters in format
        [parameter name] [parameter description] [parameter type] [parameter default value]
      </OUT>
    </parameters
  </comments>

  <version>
    list of versions changes. only the last one is displaed when funtions of
    this package are used. The format to comment version changes:
    [version number] [version date] [version author] [action: change/bug fix/creation/modification etc...] [description]
  </version>

  TAGS SHOULD BE UNIQUE FOR ANY PROCEDURE/FUNCTION DESCRIPTION!
  Tags are NOT case sensitive.

  Another tag which is used for display of package description is:
  <main_description>

</main_description>
*/

C_PACKAGE_NAME                                   CONSTANT VARCHAR2(255)   := 'ORATSG_APIDOC';

C_HTMLREPTAG_MARKER                              CONSTANT VARCHAR2(255)   := '###!!!###';

C_APITAG_MAINDES                                 CONSTANT VARCHAR2(255)   := 'main_description';
C_APITAG_COMMENTS                                CONSTANT VARCHAR2(255)   := 'comments';
C_APITAG_FUNCTION                                CONSTANT VARCHAR2(255)   := 'Function';
C_APITAG_PROCEDURE                               CONSTANT VARCHAR2(255)   := 'Procedure';
C_APITAG_RETURN                                  CONSTANT VARCHAR2(255)   := 'Return';
C_APITAG_DESCRIPTION                             CONSTANT VARCHAR2(255)   := 'Description';
C_APITAG_PARAMETERS                              CONSTANT VARCHAR2(255)   := 'Parameters';
C_APITAG_IN                                      CONSTANT VARCHAR2(255)   := 'IN';
C_APITAG_OUT                                     CONSTANT VARCHAR2(255)   := 'OUT';
C_APITAG_VERSION                                 CONSTANT VARCHAR2(255)   := 'Version';

/*
<comments>
  <function>
    getORAVC_API_HTMLDoc
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function retunes CLOB with ORAVC API procedures/functions or other correctlly commented packages or
    any other Oracle source. Clob is an HTML.
  </description>

  <parameters>
    <IN>
      [p_object_owner]                      [oracle object owner]                           [VARCHAR2]  [NULL]
      [p_object_type]                       [object type: 'PACKAGE', 'PACKAGE BODY' etc]    [VARCHAR2]  [NULL]
      [p_object_name]                       [oracle obkect name]                            [VARCHAR2]  [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getORAVC_API_HTMLDoc(
            p_object_owner            VARCHAR2
           ,p_object_type             VARCHAR2
           ,p_object_name             VARCHAR2
           ,p_object_ref              VARCHAR2
        ) RETURN CLOB;

/*
<comments>
  <function>
    getORAVC_API_HTMLDocHeader
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function retunes CLOB with generated Header for the ORAVC object documentation,
    with the list of given objects. Two array are send into this procedure.
    The first array contains names of html references for the objects which will be displayed.
    The second array contains objects names that will be displayed. Relation between array
    are set by array index.

    This function is optional for before getORAVC_API_HTMLDoc calls
  </description>

  <parameters>
    <IN>
      [p_objects_refs]         [array of names of objects references]     [NUMBER]   [NULL]
      [p_logs_to_display]      [array of objects names]                   [NUMBER]   [NULL]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getORAVC_API_HTMLDocHeader(
            p_objects_refs         T_CLOB_ARRAY
           ,p_objects_names        T_CLOB_ARRAY
         ) RETURN CLOB;

end oratsg_apidoc;
/


CREATE OR REPLACE package body oratsg_apidoc as

/*
<comments>
  <function>
    trimValues1
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes CLOB. It Trims input string removing chr(10), chr(9) and spaces
    from the left and right sides of the input string
  </description>

  <parameters>
    <IN>
      [p_value]                      [value to trim]                           [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION trimValues1(
            p_value                   CLOB
         ) RETURN CLOB IS

  l_temp_str                          CLOB := p_value;

BEGIN
    l_temp_str := TRIM(BOTH chr(10) FROM l_temp_str);
    l_temp_str := TRIM(BOTH chr(9) FROM l_temp_str);
    l_temp_str := TRIM(l_temp_str);

    RETURN l_temp_str;

END trimValues1;

/*
<comments>
  <function>
    trimValues2
  </function>

  <return>
    CLOB
  </return>

  <description>
    Function returnes CLOB. It replaces all chr(10) with spaces remouves chr(10)
    from the left and right sides of the input string
  </description>

  <parameters>
    <IN>
      [p_value]                      [value to trim]                           [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION trimValues2(
            p_value                   CLOB
         ) RETURN CLOB IS

  l_temp_str                          CLOB := p_value;

BEGIN
    l_temp_str := REPLACE(l_temp_str, chr(10), ' ');
    l_temp_str := TRIM(l_temp_str);

    RETURN l_temp_str;

END trimValues2;

/*
<comments>
  <function>
    gerParameterInfo
  </function>

  <return>
    CLOB
  </return>

  <description>
    Funtion gets information of from the block between [ ... ] in the parameters tag
  </description>

  <parameters>
    <IN>
      [p_params_str]                      [string with patameters]                           [CLOB]
      [p_infonumber]                      [occurancy of parameter bloc in the string]        [NUMBER]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION gerParameterInfo(
            p_params_str              CLOB
           ,p_infonumber              NUMBER
         ) RETURN CLOB IS

  l_result                            CLOB;
  l_tag_open                          VARCHAR2(2) := '\[';
  l_tag_close                         VARCHAR2(2) := '\]';

BEGIN
  l_result := regexp_substr(p_params_str, l_tag_open || '.*?' || l_tag_close, 1, p_infonumber, 'imn');
  l_result := LTRIM(l_result, l_tag_open);
  l_result := RTRIM(l_result, l_tag_close);

  RETURN trimValues1(l_result);

END gerParameterInfo;

/*
<comments>
  <function>
    existTag
  </function>

  <return>
    BOOLEAN
  </return>

  <description>
    Funtion checkes if the given tag and closing tag combinations exists in the given string
    starting, from the given position and counting the occurency of the tag
  </description>

  <parameters>
    <IN>
      [p_source]                      [string to search for the tag]            [CLOB]
      [p_tagname]                     [tag name to search for]                  [VARCHAR2]
      [p_position]                    [position from which to start search]     [NUMBER]    [1]
      [p_occurrence]                  [occurence that should be checked]        [NUMBER]    [1]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION existTag(
           p_source                   CLOB
          ,p_tagname                  VARCHAR2
          ,p_position                 NUMBER    := 1
          ,p_occurrence               NUMBER    := 1
         ) RETURN BOOLEAN IS
  l_tag_open                          VARCHAR2(4000) := '<' || p_tagname || '>';
  l_tag_close                         VARCHAR2(4000) := '</' || p_tagname || '>';
BEGIN
  IF regexp_instr(p_source, l_tag_open || '.*?' || l_tag_close, p_position, p_occurrence, 0, 'imn') > 0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END existTag;

/*
<comments>
  <function>
    getTagValue
  </function>

  <return>
    CLOB
  </return>

  <description>
    Funtion returnes inner value between the given tag and closing tag combinations, in the given string
    starting, from the given position and counting the occurency of the tag
  </description>

  <parameters>
    <IN>
      [p_source]                      [string to search for the tag]            [CLOB]
      [p_tagname]                     [tag name to search for]                  [VARCHAR2]
      [p_position]                    [position from which to start search]     [NUMBER]    [1]
      [p_occurrence]                  [occurence that should be checked]        [NUMBER]    [1]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getTagValue(
           p_source                   CLOB
          ,p_tagname                  VARCHAR2
          ,p_position                 NUMBER    := 1
          ,p_occurrence               NUMBER    := 1
         ) RETURN CLOB IS
  l_result                            CLOB;
  l_function_name                     VARCHAR2(64) := 'getTagValue';
  l_tag_open                          VARCHAR2(4000) := '<' || p_tagname || '>';
  l_tag_close                         VARCHAR2(4000) := '</' || p_tagname || '>';
BEGIN
  l_result := regexp_substr(p_source, l_tag_open || '.*?' || l_tag_close, p_position, p_occurrence, 'imn');
  l_result := regexp_replace(l_result, l_tag_open, '', 1, 1, 'imn');
  l_result := regexp_replace(l_result, l_tag_close, '', 1, 1, 'imn');

  RETURN l_result;

END getTagValue;

/*
<comments>
  <function>
    replaceTag
  </function>

  <return>
    CLOB
  </return>

  <description>
    Funtion returnes input string with replaces tag content by input string to replace
    By default if is replaces with empty string. If replaces fro mthe given position
    and the given occurency. (by defult bouth hvalues are 1)
  </description>

  <parameters>
    <IN>
      [p_source]                      [string to search for the tag]            [CLOB]
      [p_tagname]                     [tag name to search for]                  [VARCHAR2]
      [p_replace_string]              [string to replace tag value]             [VARCHAR2]  ['']
      [p_position]                    [position from which to start search]     [NUMBER]    [1]
      [p_occurrence]                  [occurence that should be checked]        [NUMBER]    [1]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION replaceTag(
           p_source                   CLOB
          ,p_tagname                  VARCHAR2
          ,p_replace_string           CLOB      := ''
          ,p_position                 NUMBER    := 1
          ,p_occurrence               NUMBER    := 1
         ) RETURN CLOB IS
  l_function_name                     VARCHAR2(64) := 'replaceTag';
  l_tag_open                          VARCHAR2(4000) := '<' || p_tagname || '>';
  l_tag_close                         VARCHAR2(4000) := '</' || p_tagname || '>';
BEGIN

  RETURN regexp_replace(p_source, l_tag_open || '.*?' || l_tag_close, p_replace_string, p_position, p_occurrence, 'imn');

END replaceTag;

/*
<comments>
  <function>
    getAPITag_MAINDES
  </function>

  <return>
    CLOB
  </return>

  <description>
    Funtion returnes HTML for the <main_description> tag
  </description>

  <parameters>
    <IN>
      [p_source]                      [string to search for the tag]            [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getAPITag_MAINDES(
           p_source                   CLOB
         ) RETURN CLOB IS

  l_result                            CLOB;
  l_function_name                     VARCHAR2(64) := 'getTagValue';
BEGIN
  l_result := getTagValue(p_source, C_APITAG_MAINDES);
  l_result := LTRIM(l_result, chr(10));
  RETURN l_result;
END getAPITag_MAINDES;

/*
<comments>
  <function>
    getAPITag_PROCFUNC
  </function>

  <return>
    CLOB
  </return>

  <description>
    Funtion returnes HTML for the <procedure>/<function>
  </description>

  <parameters>
    <IN>
      [p_source]                      [string to search for the tag]            [CLOB]
      [p_tagname]                     [tag name to search for]                  [VARCHAR2]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getAPITag_PROCFUNC(
           p_source                   CLOB
          ,p_tag_name                 VARCHAR2
         ) RETURN CLOB IS

  l_source                            CLOB          := '';
  l_result                            CLOB          := '';
  l_function_name                     VARCHAR2(64)  := 'getAPITag_PROCEDURES';
  l_occurrence                        NUMBER        := 1;
  l_temp_str                          CLOB          := '';
BEGIN
  dbms_lob.createtemporary(l_result, TRUE);
  dbms_lob.append(l_result, '<ul>' || chr(10));
  l_source := replaceTag(p_source, C_APITAG_MAINDES);

  WHILE existTag(l_source, C_APITAG_COMMENTS, 1, l_occurrence) LOOP
    l_temp_str := getTagValue(l_source, C_APITAG_COMMENTS, 1, l_occurrence);
    IF existTag(l_temp_str, p_tag_name) THEN
      l_temp_str := trimValues2(getTagValue(l_temp_str, p_tag_name));
      dbms_lob.append(l_result, '<li>' || chr(10));
      dbms_lob.append(l_result, '<p>' || chr(10));
      dbms_lob.append(l_result, '<a href="#' || to_char(l_occurrence) || l_temp_str || '">' || l_temp_str || '</a>' || chr(10));
      dbms_lob.append(l_result, '</p>' || chr(10));
      dbms_lob.append(l_result, '</li>' || chr(10));
    END IF;

    --unended loop protection
    l_occurrence := l_occurrence + 1;
    IF l_occurrence > 100000 THEN
      EXIT;
    END IF;

  END LOOP;

  dbms_lob.append(l_result, '</ul>' || chr(10));

  RETURN l_result;

END getAPITag_PROCFUNC;

/*
<comments>
  <function>
    getAPITag_COMMENTS
  </function>

  <return>
    CLOB
  </return>

  <description>
    Funtion returnes HTML for the <comments> tag
  </description>

  <parameters>
    <IN>
      [p_source]                      [string to search for the tag]            [CLOB]
    </IN>
  </parameters>

  <version>
    [1.0] [01-03-2012] [Andrey Tsyganov] [creation] []
  </version>

</comments>
*/
FUNCTION getAPITag_COMMENTS(
           p_source                   CLOB
         ) RETURN CLOB IS

  l_source                            CLOB          := '';
  l_result                            CLOB          := '';
  l_function_name                     VARCHAR2(64)  := 'getAPITag_PROCEDURES';
  l_occurrence                        NUMBER        := 1;
  l_temp_str                          CLOB          := '';
  l_tag_str                           CLOB          := '';
  l_tag_str2                          CLOB          := '';
  l_version_str                       CLOB          := '';
BEGIN
  l_source := replaceTag(p_source, C_APITAG_MAINDES);

  dbms_lob.createtemporary(l_result, TRUE);

  WHILE existTag(l_source, C_APITAG_COMMENTS, 1, l_occurrence) LOOP
    dbms_lob.append(l_result, '<hr>' || chr(10));
    dbms_lob.append(l_result, '<div class=comments>' || chr(10));

    l_temp_str := getTagValue(l_source, C_APITAG_COMMENTS, 1, l_occurrence);
    CASE
      WHEN existTag(l_temp_str, C_APITAG_FUNCTION) THEN
        l_tag_str := trimValues2(getTagValue(l_temp_str, C_APITAG_FUNCTION));
        dbms_lob.append(l_result, '<a id="' || to_char(l_occurrence) || l_tag_str || '"><h3>' || l_tag_str || '</h3></a>' || chr(10));
        dbms_lob.append(l_result, '<h4>' || C_APITAG_FUNCTION || ', ' || C_APITAG_RETURN || ': ' || trimValues1(getTagValue(l_temp_str, C_APITAG_RETURN)) || '</h4>' || chr(10));

      WHEN existTag(l_temp_str, C_APITAG_PROCEDURE) THEN
        l_tag_str := trimValues2(getTagValue(l_temp_str, C_APITAG_PROCEDURE));
        dbms_lob.append(l_result, '<a id="' || to_char(l_occurrence) || l_tag_str || '"><h3>' || l_tag_str || '</h3></a>' || chr(10));
        dbms_lob.append(l_result, '<h4>' || C_APITAG_PROCEDURE || '</h4>' || chr(10));

      ELSE
        dbms_lob.append(l_result, 'Unknown function/procedure' || chr(10));

    END CASE;

    dbms_lob.append(l_result, '<p>' || ORAVC_ENGINE.getClobForHTML(getTagValue(l_temp_str, C_APITAG_DESCRIPTION)) || '</p>' || chr(10));

    l_tag_str := getTagValue(l_temp_str, C_APITAG_PARAMETERS);
    IF LENGTH(l_tag_str) > 0 THEN
      dbms_lob.append(l_result, '<h4>' || C_APITAG_PARAMETERS || '</h4>' || chr(10));
      dbms_lob.append(l_result, '<table>' || chr(10));
      dbms_lob.append(l_result, '<tr>' || chr(10));
      dbms_lob.append(l_result, '<th>Parameter</th>' || chr(10));
      dbms_lob.append(l_result, '<th>Description</th>' || chr(10));
      dbms_lob.append(l_result, '<th>Type</th>' || chr(10));
      dbms_lob.append(l_result, '<th></th>' || chr(10));
      dbms_lob.append(l_result, '<th>Default</th>' || chr(10));
      dbms_lob.append(l_result, '</tr>' || chr(10));

      l_tag_str := getTagValue(l_temp_str, C_APITAG_IN);
      l_tag_str2 := '';
      FOR iChar IN 1..LENGTH(l_tag_str) LOOP
        IF SUBSTR(l_tag_str, iChar, 1) = chr(10) THEN
          IF INSTR(l_tag_str2, ']') > 0 THEN
            dbms_lob.append(l_result, '<tr>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 1)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 2)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 3)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, C_APITAG_IN || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 4)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '</tr>' || chr(10));
            l_tag_str2 := '';
          END IF;
        ELSE
          l_tag_str2 := l_tag_str2 || SUBSTR(l_tag_str, iChar, 1);
        END IF;
      END LOOP;

      l_tag_str := getTagValue(l_temp_str, C_APITAG_OUT);
      l_tag_str2 := '';
      FOR iChar IN 1..LENGTH(l_tag_str) LOOP
        IF SUBSTR(l_tag_str, iChar, 1) = chr(10) THEN
          IF INSTR(l_tag_str2, ']') > 0 THEN
            dbms_lob.append(l_result, '<tr>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 1)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 2)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 3)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, C_APITAG_OUT || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '<td>' || chr(10));
            dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 4)) || chr(10));
            dbms_lob.append(l_result, '</td>' || chr(10));
            dbms_lob.append(l_result, '</tr>' || chr(10));
            l_tag_str2 := '';
          END IF;
        ELSE
          l_tag_str2 := l_tag_str2 || SUBSTR(l_tag_str, iChar, 1);
        END IF;
      END LOOP;

      dbms_lob.append(l_result, '</table>' || chr(10));
    END IF;


    l_tag_str := getTagValue(l_temp_str, C_APITAG_VERSION);
    l_tag_str2 := '';
    FOR iChar IN 1..LENGTH(l_tag_str) LOOP
      IF SUBSTR(l_tag_str, iChar, 1) = chr(10) THEN
        IF INSTR(l_tag_str2, ']') > 0 THEN
          l_version_str := '';
          l_version_str := l_version_str || '<h4>' || C_APITAG_VERSION || '</h4>' || chr(10);
          l_version_str := l_version_str || '<table>' || chr(10);
          l_version_str := l_version_str || '<tr>' || chr(10);
          l_version_str := l_version_str || '<th>Number</th>' || chr(10);
          l_version_str := l_version_str || '<td>' || chr(10);
          l_version_str := l_version_str || ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 1)) || chr(10);
          l_version_str := l_version_str || '</td>' || chr(10);
          l_version_str := l_version_str || '</tr>' || chr(10);
          l_version_str := l_version_str || '<tr>' || chr(10);
          l_version_str := l_version_str || '<th>Date</th>' || chr(10);
          l_version_str := l_version_str || '<td>' || chr(10);
          l_version_str := l_version_str || ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 2)) || chr(10);
          l_version_str := l_version_str || '</td>' || chr(10);
          l_version_str := l_version_str || '</tr>' || chr(10);
          l_version_str := l_version_str || '<tr>' || chr(10);
          l_version_str := l_version_str || '<th>Creator</th>' || chr(10);
          l_version_str := l_version_str || '<td>' || chr(10);
          l_version_str := l_version_str || ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 3)) || chr(10);
          l_version_str := l_version_str || '</td>' || chr(10);
          l_version_str := l_version_str || '</tr>' || chr(10);
          l_version_str := l_version_str || '<tr>' || chr(10);
          l_version_str := l_version_str || '<th>Action</th>' || chr(10);
          l_version_str := l_version_str || '<td>' || chr(10);
          l_version_str := l_version_str || ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 4)) || chr(10);
          l_version_str := l_version_str || '</td>' || chr(10);
          l_version_str := l_version_str || '</tr>' || chr(10);
          l_version_str := l_version_str || '<tr>' || chr(10);
          l_version_str := l_version_str || '<th>Description</th>' || chr(10);
          l_version_str := l_version_str || '<td>' || chr(10);
          l_version_str := l_version_str || ORAVC_ENGINE.getClobForHTML(gerParameterInfo(l_tag_str2, 5)) || chr(10);
          l_version_str := l_version_str || '</td>' || chr(10);
          l_version_str := l_version_str || '</tr>' || chr(10);
          l_version_str := l_version_str || '</table>' || chr(10);
          l_tag_str2 := '';
        END IF;
      ELSE
        l_tag_str2 := l_tag_str2 || SUBSTR(l_tag_str, iChar, 1);
      END IF;
    END LOOP;

    dbms_lob.append(l_result, l_version_str || chr(10));

    --unended loop protection
    l_occurrence := l_occurrence + 1;
    IF l_occurrence > 100000 THEN
      EXIT;
    END IF;

    dbms_lob.append(l_result, '</div>' || chr(10));
  END LOOP;

  RETURN l_result;

END getAPITag_COMMENTS;

/*
<comments>
  in package specification
</comments>
*/
FUNCTION getORAVC_API_HTMLDoc(
            p_object_owner            VARCHAR2
           ,p_object_type             VARCHAR2
           ,p_object_name             VARCHAR2
           ,p_object_ref              VARCHAR2
         ) RETURN CLOB IS
  l_result                            CLOB         := NULL;
  l_function_name                     VARCHAR2(64) := 'getORAVC_API_HTMLDoc';
  l_bgcolor                           VARCHAR2(20);
  l_source                            CLOB         := '';

  CURSOR obj_source_cur IS
    SELECT line, text
      FROM all_source
     WHERE owner = p_object_owner
       AND type = p_object_type
       AND name = p_object_name
     ORDER BY line;

BEGIN
  FOR obj_source_rec IN obj_source_cur LOOP
      l_source := l_source || obj_source_rec.text;
  END LOOP;

  dbms_lob.createtemporary(l_result, TRUE);
  dbms_lob.append(l_result, '<hr>' || chr(10));
  dbms_lob.append(l_result, '<p id="' || p_object_ref || '"></p>' || chr(10));
  dbms_lob.append(l_result, '<table width=100%>'  || chr(10));
  dbms_lob.append(l_result, '<tr>'                               || chr(10));
  dbms_lob.append(l_result, '<th>Object name</th>'                       || chr(10));
  dbms_lob.append(l_result, '<th>'                       || chr(10));
  dbms_lob.append(l_result, p_object_name                        || chr(10));
  dbms_lob.append(l_result, '</th>'                       || chr(10));
  dbms_lob.append(l_result, '</tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<th>Object owner</th>'                       || chr(10));
  dbms_lob.append(l_result, '<td>'                        || chr(10));
  dbms_lob.append(l_result,  p_object_owner                       || chr(10));
  dbms_lob.append(l_result, '</td>'                       || chr(10));
  dbms_lob.append(l_result, '</tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<th>Object type</th>'                       || chr(10));
  dbms_lob.append(l_result, '<td>'                        || chr(10));
  dbms_lob.append(l_result, p_object_type                        || chr(10));
  dbms_lob.append(l_result, '</td>'                       || chr(10));
  dbms_lob.append(l_result, '</tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<tr><td><br></td></tr>'                               || chr(10));
  dbms_lob.append(l_result, '<tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<th>Description</th>'                       || chr(10));
  dbms_lob.append(l_result, '<td>'              || chr(10));
  dbms_lob.append(l_result, ORAVC_ENGINE.getClobForHTML(getAPITag_MAINDES(l_source))                     || chr(10));
  dbms_lob.append(l_result, '</td>'                       || chr(10));
  dbms_lob.append(l_result, '</tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<th>Procedures</th>'                       || chr(10));
  dbms_lob.append(l_result, '<td>'                       || chr(10));
  dbms_lob.append(l_result, getAPITag_PROCFUNC(l_source, c_APITAG_PROCEDURE)                     || chr(10));
  dbms_lob.append(l_result, '</td>'                       || chr(10));
  dbms_lob.append(l_result, '</tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<th>Functions</th>'                       || chr(10));
  dbms_lob.append(l_result, '<td>'                       || chr(10));
  dbms_lob.append(l_result, getAPITag_PROCFUNC(l_source, C_APITAG_FUNCTION)                     || chr(10));
  dbms_lob.append(l_result, '</td>'                       || chr(10));
  dbms_lob.append(l_result, '</tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<tr>'                                               || chr(10));
  dbms_lob.append(l_result, '<td colspan=2>'                       || chr(10));
  dbms_lob.append(l_result, getAPITag_COMMENTS(l_source)                     || chr(10));
  dbms_lob.append(l_result, '</td>'                       || chr(10));
  dbms_lob.append(l_result, '</tr>'                                               || chr(10));

  dbms_lob.append(l_result, '</table>'                                                          || chr(10));

  RETURN l_result;

EXCEPTION
  WHEN OTHERS THEN
    ORAVC_ENGINE.add2log(
       p_message_type    => 'ERROR'
      ,p_message_text    => 'ERROR IN ' || C_PACKAGE_NAME || '.' || l_function_name || chr(10)
      || SQLCODE || chr(10) || l_function_name || chr(10)
      || SQLERRM
      ,p_message_place    => C_PACKAGE_NAME || '.' || l_function_name
      );
    RETURN SQLERRM || chr(10) || ' check Version Controll log ';

END getORAVC_API_HTMLDoc;


/*
<comments>
  in package specification
</comments>
*/
FUNCTION getORAVC_API_HTMLDocHeader(
            p_objects_refs         T_CLOB_ARRAY
           ,p_objects_names        T_CLOB_ARRAY
         ) RETURN CLOB IS

  l_result                            CLOB           := '';
  l_function_name                     VARCHAR2(64)   := 'getORAVC_API_HTMLDocHeader';
  l_occurrence                        NUMBER         := 1;
  l_temp_str                          CLOB := '';
BEGIN
  dbms_lob.createtemporary(l_result, TRUE);
  IF p_objects_names.COUNT > 0  AND p_objects_refs.COUNT > 0 AND p_objects_names.COUNT = p_objects_refs.COUNT THEN
    dbms_lob.append(l_result, '<h3>Objects API documentation</h3>' || chr(10));

    dbms_lob.append(l_result, '<ul>' || chr(10));

    FOR iObj IN 1..p_objects_names.COUNT LOOP
      dbms_lob.append(l_result, '<li>' || chr(10));
      dbms_lob.append(l_result, '<p>' || chr(10));
      dbms_lob.append(l_result, '<a href="#' || p_objects_refs(iObj) || '">' || p_objects_names(iObj) || '</a>' || chr(10));
      dbms_lob.append(l_result, '</p>' || chr(10));
      dbms_lob.append(l_result, '</li>' || chr(10));
    END LOOP;


   dbms_lob.append(l_result, '</ul>' || chr(10));
  ELSE
    dbms_lob.append(l_result, '<h4>Wrong call to create header, arrays are of the wrong length</h4>' || chr(10));

  END IF;

  RETURN l_result;


END getORAVC_API_HTMLDocHeader;

end oratsg_apidoc;
/
